﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Entities;
using System.Data.SqlClient;

namespace DAL
{
    public class StationDAL : BaseObject
    {
        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private Station GetObjectFromReader(IDataReader reader)
        {
            Station temp = new Station();
            temp.StationID = (reader["StationID"] is DBNull) ? int.MinValue : (System.Int32)reader["StationID"];
            temp.StastionName = (reader["StastionName"] is DBNull) ? String.Empty : (System.String)reader["StastionName"];
            return temp;
        }

        ///<summary>
        ///Get List Station and store in a list 
        ///</summary>
        public Station Get_Station_By_ID(int StationID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsByID, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                conn.Open();
                Station obj = new Station();

                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        obj = GetObjectFromReader(reader);
                    }
                }
                conn.Close();
                return obj;
            }
        }

        ///<summary>li
        ///Get List Station as DataTable 
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
        public List<Station> GetList_Station(string whereSql, string orderBy)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SqlWhere", whereSql));
                cmd.Parameters.Add(new SqlParameter("@SqlOrderBy", orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    List<Station> lst = new List<Station>();

                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            lst.Add(GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return lst;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>li
        ///Get All Station as DataTable 
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
        public List<Station> GetAll_Station()
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsAll, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    List<Station> lst = new List<Station>();

                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            lst.Add(GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return lst;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
        public DataTable Get_StationAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsPaged, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Insert new Station
        ///</summary>
        public bool Insert_Station(Station temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_InserttbStation, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                cmd.Parameters.Add(new SqlParameter("@StastionName", temp.StastionName));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return true;
        }

        ///<summary>
        ///Update a Station
        ///</summary>
        public bool Update_Station(Station temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_UpdatetbStation, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                cmd.Parameters.Add(new SqlParameter("@StastionName", temp.StastionName));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return true;
        }

        ///<summary>
        ///Delete a Station
        ///</summary>
        public bool Delete_Station(Station temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_DeletetbStation, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return true;
        }

        public List<Station> Get_Start_End_Station(string TrainID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("searchStationByTrainID", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TrainID", TrainID));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    List<Station> lst = new List<Station>();

                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            lst.Add(GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return lst;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public DataTable Search_Station(string whereSql, string orderBy)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public DataTable Get_Station_All()
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbStationsAll, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        #endregion

    }
}
